package util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel导入导出工具类
 */
public class ExcelUtil {
    public static List<String> readByFilePath(String path) {
        StringBuilder s = new StringBuilder();
        List<String> list = new ArrayList<>();
        File file = new File(path);
        if (!file.exists())
            System.out.println("文件不存在");
        try {
            Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
            Sheet sheet = workbook.getSheetAt(0);
            int rowLength = sheet.getLastRowNum() + 1;
            Row row = sheet.getRow(0);
            int colLength = row.getLastCellNum();
            Cell cell = row.getCell(0);
            for (int i = 0; i < rowLength; i++) {
                row = sheet.getRow(i);
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        if (cell.getCellType()== Cell.CELL_TYPE_NUMERIC&& HSSFDateUtil.isCellDateFormatted(cell)){
                            Date date = cell.getDateCellValue();
                            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:dd");
                            String time =  formater.format(date);
                            s.append(time).append("`");
                        }else{
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            s.append(cell != null ? cell.getStringCellValue() : "").append("`");
                        }

                    }

                }
                list.add(s.toString());
                s = new StringBuilder();
            }
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        return list;
    }
}
